﻿Imports System.Data.SqlClient
Public Class SQL_worker

    ''' <summary>
    ''' vytvori databazu na cielove miesto
    ''' </summary>
    ''' <param name="database">Database Name</param>
    ''' <param name="scriptDatabase">Script for creating Database</param>
    ''' <param name="scriptTable">Script for creating Tables</param>
    ''' <param name="serverName">Server name</param>
    ''' <remarks></remarks>
    Public Shared Sub createDB(ByVal database As String, ByVal scriptDatabase As String, ByVal scriptTable As String, ByVal scriptSystem As String, ByVal serverName As String)
        Dim ableToCreateDatabase As Boolean = True
        ' zisti ci uz dana databaza neexistuje ak ano opyta sa ci ju chce uzivatel prepisat
        Try
            If (SQL_worker.existDatabase(database, serverName)) Then
                Dim reply As DialogResult = MessageBox.Show("Databáza s menom '" + database + "' už existuje. Chcete ju nahradiť?", "Generovanie Databázy", _
                  MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)

                If reply = DialogResult.Yes Then
                    dropDatabase(database, serverName)
                Else
                    ableToCreateDatabase = False
                End If
            End If
            If (ableToCreateDatabase) Then


                'connection string je bez initial catalog = Master
                Dim connString As New String("Data Source=" + serverName + ";User Id=;Password=;Integrated Security = true;")

                'Dim connString As New String("Data Source=" + serverName + ";Initial Catalog=Master;User Id=;Password=;Integrated Security = true;")
                Dim myConn As SqlConnection = New SqlConnection(connString)

                Try
                    myConn.Open()
                Catch ex As Exception
                    MessageBox.Show("CHYBA - Nepodarilo sa pripojiť k databáze: " + ex.Message)
                End Try

                If (myConn.State = ConnectionState.Open) Then
                    Try

                        Dim cmdDatabase As New SqlCommand(scriptDatabase, myConn)
                        cmdDatabase.ExecuteNonQuery()

                        'scriptSystem = scriptSystem.Replace("GO", "")
                        'Dim cmdSystem As New SqlCommand(scriptSystem, myConn)
                        'cmdSystem.ExecuteNonQuery()

                        '   scriptTable = scriptTable.Replace("-1", "1")   ' dano
                        Dim cmdTable As New SqlCommand(scriptTable, myConn)
                        cmdTable.ExecuteNonQuery()



                    Catch ex As Exception
                        MessageBox.Show("CHYBA - Nepodatil sa spustiť dotaz na vytvorenie databázy: " + ex.Message)
                    End Try

                End If
                myConn.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' Zisti ci databaza uz existuje
    ''' </summary>
    ''' <param name="database">nazov databazy</param>
    ''' <param name="serverName">nazov servera</param>
    ''' <remarks></remarks>
    Private Shared Function existDatabase(ByVal database As String, ByVal serverName As String) As Boolean

        Dim connString As New String("Data Source=" + serverName + ";User Id=;Password=;Integrated Security = true;database=master")
        Dim myConn As SqlConnection = New SqlConnection(connString)
        Dim bRet As Boolean = False
        Try
            myConn.Open()
        Catch ex As Exception
            Throw New Exception("CHYBA - Nepodarilo sa pripojiť k databáze: " + ex.Message)
        End Try
        Dim script As String = "select * from master.dbo.sysdatabases where name='" + database + "'"

        Dim cmdDatabase As New SqlCommand(script, myConn)
        Dim r As SqlDataReader = Nothing
        Try
            r = cmdDatabase.ExecuteReader()
        Catch ex As Exception
            MessageBox.Show("CHYBA - Nepodarilo sa zistiť či existuje databáza " & database & ": " + ex.Message)
        End Try
        If (r.HasRows()) Then
            myConn.Close()
            Return True
        End If
        myConn.Close()
        Return False
    End Function

    Private Shared Sub dropDatabase(ByVal database As String, ByVal serverName As String)

        Dim connString As New String("Data Source=" + serverName + ";User Id=;Password=;Integrated Security = true;database=master")
        Dim myConn As SqlConnection = New SqlConnection(connString)
        Dim bRet As Boolean = False
        Try
            myConn.Open()
        Catch ex As Exception
            MessageBox.Show("CHYBA - Nepodarilo sa pripojiť k databáze: " + ex.Message)
        End Try
        Dim script As String = "Drop Database " + database
        Dim cmdDropDatabase As New SqlCommand(script, myConn)

        Try
            cmdDropDatabase.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show("CHYBA - Nepodaril sa DROP databázy " & database & ": " + ex.Message)
        End Try

    End Sub


End Class
